﻿using System;
using System.Data;
using System.Data.Common;
using System.Collections.Generic;
using Runda.EShop.Common.Utility;
using Runda.EShop.Common.Entity;
using System.Data.SqlClient;
using System.Text;

namespace Runda.EShop.Dal
{
    /// <summary>
    /// DAL layer class dbo.Biz_Order_Detail.
    /// </summary>
    public partial class BizOrderDetailDal 
    {
        public void BulkInsert(List<BizOrderDetailEntity> list)
        {
            SqlBulkCopy bulkCopy;

            if (base.Tran != null)
            {
                SqlConnection cnx = Tran.Connection as SqlConnection;
                SqlTransaction transaction = Tran as SqlTransaction;
                bulkCopy = new SqlBulkCopy(cnx, SqlBulkCopyOptions.CheckConstraints, transaction);
            }
            else
                bulkCopy = new SqlBulkCopy(CurrentDatabase.ConnectionString, SqlBulkCopyOptions.CheckConstraints);

            bulkCopy.BulkCopyTimeout = 360;
            bulkCopy.DestinationTableName = "Biz_Order_Detail";

            DataTable dataTable = new DataTable();
            DataColumn col0 = dataTable.Columns.Add("DetailID", typeof(System.Guid));
            col0.AllowDBNull = false;
            DataColumn col1 = dataTable.Columns.Add("OrderID", typeof(System.Guid));
            col1.AllowDBNull = true;
            DataColumn col2 = dataTable.Columns.Add("ProductID", typeof(System.Guid));
            col2.AllowDBNull = true;
            DataColumn col3 = dataTable.Columns.Add("ProductName", typeof(System.String));
            col3.AllowDBNull = true;
            DataColumn col4 = dataTable.Columns.Add("ProductStd", typeof(System.String));
            col4.AllowDBNull = true;
            DataColumn col5 = dataTable.Columns.Add("Unit", typeof(System.String));
            col5.AllowDBNull = true;
            DataColumn col6 = dataTable.Columns.Add("Quantity", typeof(System.Decimal));
            col6.AllowDBNull = true; 
            DataColumn col8 = dataTable.Columns.Add("HopeDate", typeof(System.DateTime));
            col8.AllowDBNull = true;
            DataColumn col9 = dataTable.Columns.Add("Remark", typeof(System.String));
            col9.AllowDBNull = true;
            DataColumn col10 = dataTable.Columns.Add("Deleted", typeof(System.Boolean));
            col10.AllowDBNull = true;

            DataColumn col11 = dataTable.Columns.Add("ProductCode", typeof(System.String));
            col11.AllowDBNull = true;

            DataColumn col12 = dataTable.Columns.Add("BatchCode", typeof(System.String));
            col12.AllowDBNull = true;
            DataColumn col13 = dataTable.Columns.Add("DueDate", typeof(System.DateTime));
            col13.AllowDBNull = true;
            DataColumn col14 = dataTable.Columns.Add("Inventory", typeof(System.Decimal));
            col14.AllowDBNull = true;
            DataColumn col15 = dataTable.Columns.Add("FollowResult", typeof(System.Int32));
            col14.AllowDBNull = true;

            bulkCopy.ColumnMappings.Add("DetailID", "DetailID");
            bulkCopy.ColumnMappings.Add("OrderID", "OrderID");
            bulkCopy.ColumnMappings.Add("ProductID", "ProductID");
            bulkCopy.ColumnMappings.Add("ProductName", "ProductName");
            bulkCopy.ColumnMappings.Add("ProductStd", "ProductStd");
            bulkCopy.ColumnMappings.Add("Unit", "Unit");
            bulkCopy.ColumnMappings.Add("Quantity", "Quantity"); 
            bulkCopy.ColumnMappings.Add("HopeDate", "HopeDate");
            bulkCopy.ColumnMappings.Add("Remark", "Remark");
            bulkCopy.ColumnMappings.Add("Deleted", "Deleted");
            bulkCopy.ColumnMappings.Add("ProductCode", "ProductCode");
            bulkCopy.ColumnMappings.Add("BatchCode", "BatchCode");
            bulkCopy.ColumnMappings.Add("DueDate", "DueDate");
            bulkCopy.ColumnMappings.Add("Inventory", "Inventory");
            bulkCopy.ColumnMappings.Add("FollowResult", "FollowResult");

            foreach (BizOrderDetailEntity entity in list)
            {
                DataRow row = dataTable.NewRow();

                row["DetailID"] = entity.DetailID;
                row["OrderID"] = entity.OrderID != Guid.Empty ? (object)entity.OrderID : System.DBNull.Value;
                row["ProductID"] = entity.ProductID != Guid.Empty ? (object)entity.ProductID : System.DBNull.Value;
                row["ProductName"] = entity.ProductName != null ? (object)entity.ProductName : System.DBNull.Value;
                row["ProductStd"] = entity.ProductStd != null ? (object)entity.ProductStd : System.DBNull.Value;
                row["Unit"] = entity.Unit != null ? (object)entity.Unit : System.DBNull.Value;
                row["Quantity"] = entity.Quantity != decimal.MinValue ? (object)entity.Quantity : System.DBNull.Value; 
                row["HopeDate"] = entity.HopeDate != DateTime.MinValue ? (object)entity.HopeDate : System.DBNull.Value;
                row["Remark"] = entity.Remark != null ? (object)entity.Remark : System.DBNull.Value;
                row["Quantity"] = (object)entity.Quantity;
                row["Deleted"] = (object)entity.Deleted;
                row["ProductCode"] = entity.ProductCode != null ? (object)entity.ProductCode : System.DBNull.Value;
                row["BatchCode"] = entity.BatchCode != null ? (object)entity.BatchCode : System.DBNull.Value;
                row["DueDate"] = entity.DueDate != DateTime.MinValue ? (object)entity.DueDate : System.DBNull.Value;
                row["Inventory"] = entity.Inventory != decimal.MinValue ? (object)entity.Inventory : System.DBNull.Value;
                row["FollowResult"] = entity.FollowResult != int.MinValue ? (object)entity.FollowResult : System.DBNull.Value;

                dataTable.Rows.Add(row);
            }

            bulkCopy.WriteToServer(dataTable);
        }

        public DataTable GetOrderDetail(List<SqlDbParameter> parms, string OrderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            StringBuilder sqlSb = new StringBuilder(@"SELECT {0} * FROM (select B.CustomerID,A.ProductID,A.Quantity,B.OrderDate,A.Deleted
,A.BatchCode,A.DueDate 
from Biz_Order_Detail A JOIN Biz_Order B
ON A.OrderID = B.OrderID ) as TB WHERE Deleted=0 {1}");
            string sqlStr = SqlDbParameter.BuildSqlString(sqlSb.ToString(), parms);

            string CmdText = @"WITH T AS
(
SELECT ROW_NUMBER() OVER(ORDER BY {1} ) AS RowNumber, * 
  FROM ({0}) as A
)
SELECT * from T WHERE RowNumber > @StartRowNum AND  RowNumber <= @EndRowNum
select count(*) from ({0}) as B 
";

            CmdText = string.Format(CmdText, sqlStr, OrderBy);

            DbCommand dbCommand = this.CurrentDatabase.GetSqlStringCommand(CmdText);
            CurrentDatabase.AddInParameter(dbCommand, "@StartRowNum", System.Data.DbType.Int32, (PageIndex) * PageSize);
            CurrentDatabase.AddInParameter(dbCommand, "@EndRowNum", System.Data.DbType.Int32, (PageIndex + 1) * PageSize);
            foreach (SqlDbParameter parameter in parms)
            {
                parameter.SetDbCommond(dbCommand);
            }

            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            RecordCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            return ds.Tables[0];
        }

        public DataTable GetOrderDetail(Guid OrderID)
        {
            string sql = "SELECT * FROM Biz_Order_Detail WHERE Deleted=0 and OrderID=@OrderID";

            DbCommand dbcommand = CurrentDatabase.GetSqlStringCommand(sql);
            CurrentDatabase.AddInParameter(dbcommand, "@OrderID", DbType.Guid, OrderID);

            return CurrentDatabase.ExecuteDataSet(dbcommand).Tables[0];

        }

        public void UpdateOrderDetailFollowResult(Guid detailID, int FollowResult)
        {
            string sql = "UPDATE Biz_Order_Detail SET FollowResult=@FollowResult WHERE DetailID=@DetailID";

            DbCommand dbcommand = CurrentDatabase.GetSqlStringCommand(sql);
            CurrentDatabase.AddInParameter(dbcommand, "@DetailID", DbType.Guid, detailID);
            CurrentDatabase.AddInParameter(dbcommand, "@FollowResult", DbType.Int32, FollowResult);

            if (base.Tran != null)
                CurrentDatabase.ExecuteNonQuery(dbcommand, Tran);
            else
                CurrentDatabase.ExecuteNonQuery(dbcommand);
        }

        /// <summary>
        /// 检查是否存在空订单
        /// </summary>
        /// <param name="CustomerID"></param>
        /// <param name="ProductID"></param>
        /// <returns></returns>
        public bool ExistsOrderDetail(Guid CustomerID, Guid ProductID)
        {
            string sql = @"IF EXISTS (
select A.DetailID from Biz_Order_Detail A
JOIN Biz_Order B ON A.OrderID = B.OrderID
WHERE A.ProductID=@ProductID AND B.OrderType=2 AND ISNULL(A.FollowResult,0) <> 1
AND B.CustomerID=@CustomerID
)
BEGIN
SELECT 1
END
ELSE
BEGIN
SELECT 0
END";
            DbCommand dbcommand = CurrentDatabase.GetSqlStringCommand(sql);
            CurrentDatabase.AddInParameter(dbcommand, "@ProductID", DbType.Guid, ProductID);
            CurrentDatabase.AddInParameter(dbcommand, "@CustomerID", DbType.Guid, CustomerID);

            int val = Convert.ToInt32(CurrentDatabase.ExecuteScalar(dbcommand));

            return val == 1;
        }
    }
}
